/*
 * Created by SharpDevelop.
 * User: florian
 * Date: 17.06.2006
 * Time: 10:26
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.ComponentModel;
using System.Text;
using System.Data;

namespace SqlScriptCreator.Generators
{
	/// <summary>
	/// Description of SelectRowIntoVariablesGenerator.
	/// </summary>
	public class SelectRowIntoVariablesGenerator : IScriptGenerator
	{
		private String variablePrefix;
		
		[CategoryAttribute("Variable settings"),
		                   DescriptionAttribute("Characters that will be prepended to every generated variable.")]
		public string VariablePrefix {
			get {
				return variablePrefix;
			}
			set {
				variablePrefix = value;
			}
		}
		private String variableSuffix;
		
		[CategoryAttribute("Variable settings"),
		 DescriptionAttribute("Characters that will be appended to every generated variable.")]
		public string VariableSuffix {
			get {
				return variableSuffix;
			}
			set {
				variableSuffix = value;
			}
		}
		
		public SelectRowIntoVariablesGenerator()
		{
		}
		
		override public string ToString() {
			return "Select one row into variables";
		}

        public string generateScript(String query, DataTable dtQueryResult, DataTable dtParameters)
		{	
            StringBuilder sb = new StringBuilder();
            String[] parameterNames = new String[dtParameters.Rows.Count];
            String[] variableNames = new String[dtParameters.Rows.Count];
            int counter = 0;

            foreach (DataRow row in dtParameters.Rows)
            {
                int columnSize = Convert.ToInt32(row["ColumnSize"]);
                String dataType = row["DataTypeName"].ToString();
                String columnName = row["BaseColumnName"].ToString();

                if (SqlTools.isValidForLocalVariable(dataType))
                {
                    parameterNames[counter] = columnName;
                    variableNames[counter] = "@" + VariablePrefix + columnName + VariableSuffix;
                    sb.AppendLine("DECLARE " + variableNames[counter] + " " + 
                        SqlTools.getDatatypeExpression(dataType, columnSize));
                    counter++;
                }
            }
            sb.AppendLine("");
            sb.AppendLine("SELECT TOP 1");
            for (int i = 0; i < variableNames.Length; i++)
            {
                sb.Append("\t" + variableNames[i] + " = " + parameterNames[i]);
                if (i < variableNames.Length - 1) sb.AppendLine(",");
                else sb.AppendLine();
            }
            sb.AppendLine("-- TODO: merge the two SELECT operations");
            sb.AppendLine(query);
            sb.AppendLine("");
        	return sb.ToString();			
		}
	}
}
